/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package tuitionmanagement.DAO;


import com.sun.rowset.CachedRowSetImpl;
import tuitionmanagement.entity.StudentInfo1;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import tuitionmanagement.entity.ClassEntity;
import tuitionmanagement.entity.Student;
import tuitionmanagement.util.DBUtil;


/**
 *
 * @author SKC
 */
public class StudentDAO {

//<<<<<<< .mine
    private String getTable="select * from [Student] where StudentName like ?";

//>>>>>>> .r92

     private Connection con;
    private ResultSet rs;
    private PreparedStatement pst;
    ClassEntity cl = new ClassEntity();
   // private String getTable="SELECT * FROM Student";// where StudentName like ?";

    private String insert="insert into Student(StudentName,Birthday,Address,Phone,Email,Gender) values('?','?','?','?','?',?)";
    private String delete="delete from Student where StudentId = '?'";
    private final String sql_selectStudentIdByStudentName = "select StudentID from Student  where StudentName = ?";
    
    //private PreparedStatement ps=null;
    //private ResultSet rs=null;
    PreparedStatement ps=null;
    ResultSet r=null;
    public int DeleteStudent(int id)
    {
        int i=-1;
        Connection conn =DBUtil.getConn();
        try {

        } catch (Exception e) {
        }
        return i;
    }
    public Vector<StudentInfo1> GetTable(String nameSearch)
    {
        nameSearch="%"+nameSearch+"%";
//        String sql = "select * from Student Where StudentName like '%"+nameSearch+"%'";
        Vector<StudentInfo1> list =new Vector<StudentInfo1>();
         Connection conn =DBUtil.getConn();
        
        try{
           
            ps=conn.prepareStatement(getTable,ResultSet.TYPE_SCROLL_SENSITIVE);
            ps.setString(1, nameSearch);
            r=new CachedRowSetImpl();
            r=ps.executeQuery();
          // Statement sm =conn.createStatement();
        //  ResultSet r= sm.executeQuery("select * from [Role]")        
//            ResultSet rs = sm.executeQuery(sql);
           // r.first();
            while(r.next())
            {
                int id = r.getInt("StudentId");
                String name = r.getString("StudentName");
                String brith = r.getDate("Birthday").toString();
                String address = r.getString("Address");
                String phone = r.getString("Phone");
                String email = r.getString("Email");
                boolean ok = r.getBoolean("Gender");
                String gender = "";
                if(ok)
                    gender = "male";
                else
                    gender = "female";
                list.add(new StudentInfo1(id, name, brith, address, phone, email, gender));
           
            }
        }catch(Exception ex)
        {
            System.out.println(ex.getMessage() + "get table");
        }
        DBUtil.closeConn(conn);
        return list;
    }
    public int InsertStudent(Student student)
    {
        Connection conn =DBUtil.getConn();
        int i=-1;
        try {
            PreparedStatement ps = conn.prepareStatement(insert);
            ps.setString(1, student.getStudentName());
            ps.setDate(2,Date.valueOf(student.getBirthday().toString()));
            ps.setString(3, student.getAddress());
            ps.setString(4, student.getPhone());
            ps.setString(5,student.getEmail());
            if(student.getGender().toLowerCase().compareTo("male")==0)
                ps.setBoolean(6, true);
            else
                ps.setBoolean(6, false);
            i=ps.executeUpdate();

        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        DBUtil.closeConn(conn);
        return i;
    }

        public int getIdByStudentName(String StudentName) {
        int stId = -1;
        con = DBUtil.getConn();
        try {
            pst = con.prepareStatement(sql_selectStudentIdByStudentName);
            pst.setString(1, StudentName);
            rs = pst.executeQuery();
            while (rs.next()) {
                stId = rs.getInt("StudentId");
            }
        } catch (SQLException ex) {
            return -1;
        } finally {
            DBUtil.closeConn(con);
        }
        return stId;
    }
}
